#!/bin/bash

# 数据库恢复脚本
# 在目标服务器上运行此脚本

# 配置参数
DB_HOST="localhost"
DB_PORT="3306"
DB_NAME="teacher_info_system"
DB_USER="root"
DB_PASSWORD="cjx12345@"

# 备份文件路径（需要先上传到服务器）
BACKUP_DIR="/opt/teacher-system/database_backup"
SCHEMA_DIR="/opt/teacher-system/schema"

echo "=== 教师信息系统数据库迁移脚本 ==="
echo ""

# 1. 检查备份文件
echo "1. 检查备份文件..."
if [ ! -d "${BACKUP_DIR}" ]; then
    echo "❌ 备份目录不存在: ${BACKUP_DIR}"
    echo "请先创建目录并上传备份文件"
    exit 1
fi

# 查找最新的备份文件
BACKUP_FILE=$(find ${BACKUP_DIR} -name "teacher_info_system_backup_*.sql.gz" | sort | tail -1)
if [ -z "${BACKUP_FILE}" ]; then
    echo "❌ 未找到备份文件，请确保备份文件已上传到 ${BACKUP_DIR}"
    exit 1
fi

echo "✅ 找到备份文件: ${BACKUP_FILE}"

# 2. 创建数据库和基础结构
echo ""
echo "2. 创建数据库和基础结构..."
mysql -h${DB_HOST} -P${DB_PORT} -u${DB_USER} -p${DB_PASSWORD} < /opt/teacher-system/database_migration/3_create_server_database.sql

if [ $? -eq 0 ]; then
    echo "✅ 数据库基础结构创建成功"
else
    echo "❌ 数据库基础结构创建失败"
    exit 1
fi

# 3. 恢复数据
echo ""
echo "3. 恢复数据..."
echo "正在解压并导入备份文件..."

# 解压并导入数据
zcat ${BACKUP_FILE} | mysql -h${DB_HOST} -P${DB_PORT} -u${DB_USER} -p${DB_PASSWORD} ${DB_NAME}

if [ $? -eq 0 ]; then
    echo "✅ 数据恢复成功"
else
    echo "❌ 数据恢复失败"
    exit 1
fi

# 4. 验证数据完整性
echo ""
echo "4. 验证数据完整性..."

# 检查主要表的记录数
mysql -h${DB_HOST} -P${DB_PORT} -u${DB_USER} -p${DB_PASSWORD} ${DB_NAME} -e "
SELECT 'user' as table_name, COUNT(*) as record_count FROM user
UNION ALL
SELECT 'role' as table_name, COUNT(*) as record_count FROM role
UNION ALL
SELECT 'user_role' as table_name, COUNT(*) as record_count FROM user_role
UNION ALL
SELECT 'course_hours' as table_name, COUNT(*) as record_count FROM course_hours
UNION ALL
SELECT 'academic_positions' as table_name, COUNT(*) as record_count FROM academic_positions
UNION ALL
SELECT 'textbooks' as table_name, COUNT(*) as record_count FROM textbooks
UNION ALL
SELECT 'party_building_activities' as table_name, COUNT(*) as record_count FROM party_building_activities;
"

# 5. 更新配置
echo ""
echo "5. 应用数据库结构更新..."

# 如果有新的表结构，在这里执行
mysql -h${DB_HOST} -P${DB_PORT} -u${DB_USER} -p${DB_PASSWORD} ${DB_NAME} -e "
-- 确保所有表都使用utf8mb4字符集
ALTER TABLE user CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE role CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE user_role CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 优化表
OPTIMIZE TABLE user, role, user_role;
"

echo ""
echo "=== 数据库迁移完成 ==="
echo "✅ 数据库: ${DB_NAME}"
echo "✅ 用户: teacher_user"
echo "✅ 默认管理员: admin/admin123"
echo ""
echo "请确保应用配置文件中的数据库连接信息正确："
echo "  - spring.datasource.url=jdbc:mysql://localhost:3306/teacher_info_system"
echo "  - spring.datasource.username=teacher_user"  
echo "  - spring.datasource.password=cjx12345@"
echo ""
echo "现在可以启动应用程序了！"
